package cs757;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Class for measuring performance of queries, with and without indexes (indexes to be created in DB)
 *
 */
public class IndexPerformance {

	/**
	 * 
	 * 5 common queries that will be run indexed and non-indexed to compare performance
	 * 
	 */
	
	/**
	 * Query for testing single-result equals
	 * Use hash index on students.cgradname
	 */
	protected static final String STUDENTS_SSN_QUERY = "SELECT * FROM UWDB.students WHERE cgradname = 'Wylie Spears'";
	protected static final String STUDENTS_SSN_QUERY_2 = "SELECT * FROM UWDB2.students WHERE cgradname = 'Wylie Spears'";
	
	/**
	 * Query for testing range
	 * Use B+ index on students.iage
	 */
	protected static final String STUDENTS_AGE_QUERY = "SELECT * FROM UWDB.students WHERE iage > 19";
	protected static final String STUDENTS_AGE_QUERY_2 = "SELECT * FROM UWDB2.students WHERE iage > 19";
	
	/**
	 * Query for testing multiple-result equals
	 * Use hash index on students.cdegree
	 */
	protected static final String STUDENTS_DEGREE_QUERY = "SELECT * FROM UWDB.students WHERE cdegree = 'masters'";
	protected static final String STUDENTS_DEGREE_QUERY_2 = "SELECT * FROM UWDB2.students WHERE cdegree = 'masters'";
	
	/**
	 * Query for testing multiple indexes
	 * Use B+ index on students.cdegree and students.iage
	 */
	protected static final String STUDENTS_AGE_AND_DEGREE_QUERY = "SELECT * FROM UWDB.students WHERE iage < 19 AND cdegree = 'masters'";
	protected static final String STUDENTS_AGE_AND_DEGREE_QUERY_2 = "SELECT * FROM UWDB2.students WHERE iage < 19 AND cdegree = 'masters'";
	
	/**
	 * Query for testing multiple-result like
	 * Use hash index on students.cgradname
	 */
	protected static final String STUDENTS_NAME_LIKE_QUERY = "SELECT * FROM UWDB.students WHERE cgradname like 'W%'";
	protected static final String STUDENTS_NAME_LIKE_QUERY_2 = "SELECT * FROM UWDB2.students WHERE cgradname like 'W%'";
	
	/**
	 * Execute a query on the database and output timing information
	 * Connection is created and destroyed within this method to minimize any caching between 
	 * result sets that may be caused by repeated calls to the method
	 * 
	 * @param query
	 * @throws SQLException
	 * @throws IOException 
	 */
	private static void executeTimedQuery(String query, String userid, String password, String server) throws SQLException, IOException{
		//Connect to db
		DatabaseConnector dbConn = new DatabaseConnector();
	
//		Connection conn = dbConn.getConnection("root", "mysql", "localhost");
		Connection conn = dbConn.getConnection(userid, password, server);

		Statement stmt = conn.createStatement();
		
		long startTimeNanoSecs = System.nanoTime();
		ResultSet results = stmt.executeQuery(query);
		if (results.next()){
			//ensure some results were actually returned
			long endTimeNanoSecs = System.nanoTime();
			long totalTime = (endTimeNanoSecs-startTimeNanoSecs)/1000;
			System.out.println("Time for query: "+query+" - "+totalTime+" microseconds");
		}
		else {
			System.out.println("No results from query: "+query+", timing data may be invalid");
		}
		
		stmt.close();
		conn.close();
	}
	
	/**
	 * Read from Stdin
	 * @return a String representing the input given
	 */
	private static String readFromStdin(String prompt) throws IOException{
		System.out.print(prompt);
		
		BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
		
		return in.readLine();
	}
	
	/**
	 * Main method
	 * Run to see query performance
	 * @param args
	 */
	public static void main(String[] args) {	
		//Execute queries, timing each one
		try {
			//Prompt for the userid and password
			String server = readFromStdin("Server name (e.g. localhost): ");
			String userid = readFromStdin("Username for " + server + ": ");
			String password = readFromStdin("Password for " + userid + "@" + server + ": ");
			
			System.out.println("Non-indexed:");
			executeTimedQuery(STUDENTS_SSN_QUERY, userid, password, server);
			executeTimedQuery(STUDENTS_AGE_QUERY, userid, password, server);
			executeTimedQuery(STUDENTS_DEGREE_QUERY, userid, password, server);
			executeTimedQuery(STUDENTS_AGE_AND_DEGREE_QUERY, userid, password, server);
			executeTimedQuery(STUDENTS_NAME_LIKE_QUERY, userid, password, server);
			
			System.out.println("\n\n\nIndexed:");
			
			executeTimedQuery(STUDENTS_SSN_QUERY_2, userid, password, server);
			executeTimedQuery(STUDENTS_AGE_QUERY_2, userid, password, server);
			executeTimedQuery(STUDENTS_DEGREE_QUERY_2, userid, password, server);
			executeTimedQuery(STUDENTS_AGE_AND_DEGREE_QUERY_2, userid, password, server);
			executeTimedQuery(STUDENTS_NAME_LIKE_QUERY_2, userid, password, server);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	

	}

}
